// src/main/java/com/bookmanager/service/StatisticsService.java
package com.bookmanager.service;

import com.bookmanager.model.BorrowRecord;
import com.bookmanager.util.DatabaseUtil;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class StatisticsService {

    // 获取图书总数
    public int getTotalBooks() {
        String sql = "SELECT COUNT(*) as total FROM books";
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql);
             ResultSet rs = stmt.executeQuery()) {

            if (rs.next()) {
                return rs.getInt("total");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    // 获取用户总数
    public int getTotalUsers() {
        String sql = "SELECT COUNT(*) as total FROM users";
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql);
             ResultSet rs = stmt.executeQuery()) {

            if (rs.next()) {
                return rs.getInt("total");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    // 获取今日借阅数量
    public int getTodayBorrows() {
        String sql = "SELECT COUNT(*) as total FROM borrow_records WHERE DATE(borrow_date) = CURDATE()";
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql);
             ResultSet rs = stmt.executeQuery()) {

            if (rs.next()) {
                return rs.getInt("total");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    // 获取逾期未还数量
    public int getOverdueBooks() {
        String sql = "SELECT COUNT(*) as total FROM borrow_records WHERE status = 'borrowed' AND due_date < NOW()";
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql);
             ResultSet rs = stmt.executeQuery()) {

            if (rs.next()) {
                return rs.getInt("total");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    // 获取当前借阅数量（用户）
    public int getCurrentBorrows(String userId) {
        String sql = "SELECT COUNT(*) as total FROM borrow_records WHERE user_id = ? AND status = 'borrowed'";
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setString(1, userId);
            ResultSet rs = stmt.executeQuery();

            if (rs.next()) {
                return rs.getInt("total");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    // 获取即将到期数量（用户）
    public int getDueSoonBooks(String userId) {
        String sql = "SELECT COUNT(*) as total FROM borrow_records WHERE user_id = ? AND status = 'borrowed' AND due_date BETWEEN NOW() AND DATE_ADD(NOW(), INTERVAL 3 DAY)";
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setString(1, userId);
            ResultSet rs = stmt.executeQuery();

            if (rs.next()) {
                return rs.getInt("total");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    // 获取历史借阅数量（用户）
    public int getBorrowHistory(String userId) {
        String sql = "SELECT COUNT(*) as total FROM borrow_records WHERE user_id = ?";
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setString(1, userId);
            ResultSet rs = stmt.executeQuery();

            if (rs.next()) {
                return rs.getInt("total");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    public List<BorrowRecord> getCurrentBorrowRecords(String userId) {
        List<BorrowRecord> records = new ArrayList<>();
        String sql = "SELECT br.*, b.title as book_title, b.author as book_author " +
                "FROM borrow_records br " +
                "JOIN books b ON br.book_id = b.book_id " +
                "WHERE br.user_id = ? AND br.status = 'borrowed' " +
                "ORDER BY br.borrow_date DESC";

        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setString(1, userId);
            ResultSet rs = stmt.executeQuery();

            while (rs.next()) {
                BorrowRecord record = extractBorrowRecordFromResultSet(rs);
                records.add(record);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return records;
    }

    private BorrowRecord extractBorrowRecordFromResultSet(ResultSet rs) throws SQLException {
        BorrowRecord record = new BorrowRecord();
        record.setRecordId(rs.getInt("record_id"));
        record.setUserId(rs.getString("user_id"));
        record.setBookId(rs.getInt("book_id"));
        record.setBorrowDate(rs.getTimestamp("borrow_date").toLocalDateTime());

        Timestamp dueDate = rs.getTimestamp("due_date");
        if (dueDate != null) {
            record.setDueDate(dueDate.toLocalDateTime());
        }

        Timestamp returnDate = rs.getTimestamp("return_date");
        if (returnDate != null) {
            record.setReturnDate(returnDate.toLocalDateTime());
        }

        record.setStatus(rs.getString("status"));
        record.setBookTitle(rs.getString("book_title"));
        record.setBookAuthor(rs.getString("book_author")); // 添加作者信息

        return record;
    }

    public List<BorrowRecord> getAllBorrowRecords(String userId) {
        List<BorrowRecord> records = new ArrayList<>();
        String sql = "SELECT br.*, b.title as book_title, b.author as book_author " +
                "FROM borrow_records br " +
                "JOIN books b ON br.book_id = b.book_id " +
                "WHERE br.user_id = ? " +
                "ORDER BY br.borrow_date DESC";

        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setString(1, userId);
            ResultSet rs = stmt.executeQuery();

            while (rs.next()) {
                BorrowRecord record = extractBorrowRecordFromResultSet(rs);
                records.add(record);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return records;
    }

    public List<BorrowRecord> getReturnedBorrowRecords(String userId) {
        List<BorrowRecord> records = new ArrayList<>();
        String sql = "SELECT br.*, b.title as book_title, b.author as book_author " +
                "FROM borrow_records br " +
                "JOIN books b ON br.book_id = b.book_id " +
                "WHERE br.user_id = ? AND br.status = 'returned' " +
                "ORDER BY br.return_date DESC";

        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setString(1, userId);
            ResultSet rs = stmt.executeQuery();

            while (rs.next()) {
                BorrowRecord record = extractBorrowRecordFromResultSet(rs);
                records.add(record);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return records;
    }

    public List<BorrowRecord> getOverdueBorrowRecords(String userId) {
        List<BorrowRecord> records = new ArrayList<>();
        String sql = "SELECT br.*, b.title as book_title, b.author as book_author " +
                "FROM borrow_records br " +
                "JOIN books b ON br.book_id = b.book_id " +
                "WHERE br.user_id = ? AND br.status = 'borrowed' AND br.due_date < NOW() " +
                "ORDER BY br.due_date ASC";

        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setString(1, userId);
            ResultSet rs = stmt.executeQuery();

            while (rs.next()) {
                BorrowRecord record = extractBorrowRecordFromResultSet(rs);
                records.add(record);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return records;
    }
}
